package com.dao;

import java.io.*;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.bean.Comment;



//import java.io.*;

/**
 * @author hxd 
 */
public class CommentSQL extends AbstractSQL{
	

	public  Comment find(int cid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Comment com=null;
		String sql = "SELECT bid,title,content,day,uid,ccid FROM comment WHERE cid=?";//
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int bid=rs.getInt(1);
				String title=rs.getString(2);
				String content=rs.getString(3);
				Timestamp time=rs.getTimestamp(4);
				int uid=rs.getInt(5);
				int ccid=rs.getInt(6);
				com=new Comment(cid, title, content, uid,bid,ccid, time) ;
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return com;
	}
	public  ArrayList<Comment> findByBlog(int bid){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//直接回复博客的一级评论
		String sql = "SELECT cid,title,content,day,uid,ccid FROM comment WHERE bid=? and ccid=0 order by day desc";
		ArrayList<Comment> alist=new ArrayList<Comment>();
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int cid=rs.getInt(1);
				String title=rs.getString(2);
				String content=rs.getString(3);
				Timestamp time=rs.getTimestamp(4);
				int uid=rs.getInt(5);
				int ccid=rs.getInt(6);
				alist.add(new Comment(cid, title, content, uid, bid,ccid,time) );
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return alist;
	}
	
	public  ArrayList<Comment> findByComment(int ccid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT cid,title,content,day,bid,uid FROM comment WHERE ccid=?";//
		ArrayList<Comment> alist=new ArrayList<Comment>();
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, ccid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int cid=rs.getInt(1);
				String title=rs.getString(2);
				String content=rs.getString(3);
				Timestamp time=rs.getTimestamp(4);
				int bid=rs.getInt(5);
				int uid=rs.getInt(6);
				alist.add(new Comment(cid, title, content, uid,bid,ccid, time) );
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return alist;
	}
	
	public  ArrayList<Comment> findByUB(int bid,int uid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		ResultSet rs = null;
		String sql = "SELECT cid,title,content,day FROM comment WHERE uid=? and bid=?";//
		ArrayList<Comment> alist=new ArrayList<Comment>();
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			pstmt.setInt(2, bid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int cid=rs.getInt(1);
				String title=rs.getString(2);
				String content=rs.getString(3);
				Timestamp time=rs.getTimestamp(4);
				alist.add(new Comment(cid, title, content, uid, time) );
			}
				pstmt.close();
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return alist;
	}

	//写评论
	public  boolean writeComment(Comment com){
		String sql="insert into comment(uid,bid,ccid,title,content,day)values (?,?,?,?,?,now())";
		Connection conn = null;
		PreparedStatement pstmt = null;
			try {
				conn=getConnection();
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, com.getUid());
				pstmt.setInt(2,com.getBid());
				pstmt.setInt(3,com.getCcid());
			pstmt.setString(4, com.getTitle());
			pstmt.setString(5,com.getContent());
			pstmt.executeUpdate();
			} catch (SQLException e) {
				return false;
			}
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return true;
			} 
		
	//
	public  void updateComment(Comment comment){
		String sql="update comment set title=?,content=? where  cid=?";
		Connection conn = null;
		PreparedStatement pstmt = null;

			try {
				conn=getConnection();
				pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, comment.getTitle());
			pstmt.setString(2, comment.getContent());
			pstmt.setInt(3, comment.getId());
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
			} catch (SQLException  e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	
	//�ı���
	public  void deleteComment(int cid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM comment WHERE cid= ?";// 删除
		try {
			conn=getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cid);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}}